﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DoeObjects;
using System.Data;
using System.Data.SqlClient;

namespace DoeObjects
{
    public class Reservation
    {
        public int ReservationId { get; set; }
        public Title Title { get; set; }
        public User User { get; set; }
        public DateTime ReserveDate { get; set; }
        public DateTime AvailableDate { get; set; }
        public bool LoanRecalled { get; set; }

        private const string BASE_SQL = @"
SELECT * 
FROM Reservation
INNER JOIN title
ON title.titleId = reservation.titleId
INNER JOIN publisher
ON title.publisherId = publisher.publisherId
INNER JOIN [user]
ON reservation.userId = [user].userId
INNER JOIN usercategory
ON usercategory.usercategoryId = [user].categoryId";

        public const int RESERVATION_EXPIRES_TIMER_IN_DAYS = 7;

        /// <summary>
        /// returns all reservations for the given title
        /// </summary>
        /// <param name="titleId"></param>
        /// <returns></returns>
        public static List<Reservation> getReservations(int titleId)
        {
            List<Reservation> result = new List<Reservation>();
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand(BASE_SQL + " WHERE title.titleid = @TitleId", connection);
            cmd.Parameters.AddWithValue("@TitleId", titleId);

            try
            {
                connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                
                while (reader.Read())
                {
					result.Add(getCurrentReservation(reader));
                }
				reader.Close();
            }
            catch (SqlException er)
            {
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return result;
        }

        public static List<Reservation> getAll()
        {
            List<Reservation> result = new List<Reservation>();
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand(BASE_SQL, connection);

            try
            {
                connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    result.Add(getCurrentReservation(reader));
                }
                reader.Close();
            }
            catch (SqlException er)
            {
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return result;
        }

        /// <summary>
        /// returns the next reservation for this title. the next reservation is the one which is the oldest one for this title
        /// </summary>
        /// <param name="titleId">the titleId</param>
        /// <param name="recalled">defines if it should return a reservation where the loan was already recalled or not</param>
        /// <returns></returns>
        public static Reservation getNext(int titleId, bool recalled)
        {
            string dbquery = BASE_SQL + " WHERE Reservation.TitleId = @titleId AND Reservation.LoanRecalled = @recallValue AND AvailableDate IS NULL ORDER BY reserveDate ASC";
            int recallValue = 1;
            Reservation nextReservation = null;

            if (!recalled)
            {
                recallValue = 0;   
            }

            SqlConnection sqlCon = new SqlConnection(Settings.ConnectionString);
            SqlCommand sqlCmd = new SqlCommand(dbquery,sqlCon);
            sqlCmd.Parameters.AddWithValue("@titleId", titleId);
            sqlCmd.Parameters.AddWithValue("@recallValue", recallValue);

            try
            {
                sqlCon.Open();
                SqlDataReader dar = sqlCmd.ExecuteReader();

                if (dar.Read())
                {
                    nextReservation = getCurrentReservation(dar);
                }
                dar.Close();

            }
            catch (SqlException er)
            {
                throw er;
            }
            finally 
            {
                sqlCon.Close();
            }

            return nextReservation;

        }

        /// <summary>
        /// creates a new reservation in the database. The AvailableDate is set to NULL and the LoanRecalled column will be set to false
        /// </summary>
        /// <param name="reservation"></param>
        /// <returns></returns>
        public static int add(Reservation reservation)
        {
            int lastInsertedId = 0;
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
			SqlCommand cmd = new SqlCommand("INSERT INTO Reservation (TitleId, UserId, ReserveDate, AvailableDate, LoanRecalled) OUTPUT INSERTED.reservationId VALUES (@TitleId, @UserId, GETDATE() , @AvailableDate, @LoanRecalled)", connection);

            cmd.Parameters.AddWithValue("@TitleId", reservation.Title.TitleId);
            cmd.Parameters.AddWithValue("@UserId", reservation.User.UserId);
			//cmd.Parameters.AddWithValue("@ReserveDate", reservation.ReserveDate);
            cmd.Parameters.AddWithValue("@AvailableDate", (reservation.AvailableDate == DateTime.MinValue) ? DBNull.Value : (object)reservation.AvailableDate);
            cmd.Parameters.AddWithValue("@LoanRecalled", (reservation.LoanRecalled) ? 1 : 0);

            try
            {
                connection.Open();
                Object res = cmd.ExecuteScalar();
                if (res != null)
                {
                    lastInsertedId = (int)res;
                }
            }
            catch (SqlException er)
            {
                //oo
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return lastInsertedId;
        }

        /// <summary>
        /// updates a reservation in the database
        /// </summary>
        /// <param name="reservation"></param>
        /// <returns></returns>
        public static bool update(Reservation reservation)
        {
            int affectedRows = 0;
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("UPDATE Reservation SET TitleId = @TitleId, UserId = @UserId, ReserveDate = @ReserveDate, AvailableDate = @AvailableDate, LoanRecalled = @LoanRecalled WHERE reservationId = @ReservationId", connection);


            cmd.Parameters.AddWithValue("@ReservationId", reservation.ReservationId);
            cmd.Parameters.AddWithValue("@TitleId", reservation.Title.TitleId);
            cmd.Parameters.AddWithValue("@UserId", reservation.User.UserId);
            cmd.Parameters.AddWithValue("@ReserveDate", reservation.ReserveDate);
            cmd.Parameters.AddWithValue("@AvailableDate", (reservation.AvailableDate == DateTime.MinValue) ? DBNull.Value : (Object)reservation.AvailableDate);
            cmd.Parameters.AddWithValue("@LoanRecalled", reservation.LoanRecalled);

            try
            {
                connection.Open();
                affectedRows = cmd.ExecuteNonQuery();
            }
            catch (SqlException er)
            {
                //oo
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return (affectedRows > 0);
        }

        /// <summary>
        /// deletes the reservation with the given id
        /// </summary>
        /// <param name="reservationId"></param>
        /// <returns></returns>
        public static bool delete(int reservationId)
        {
            int affectedRows = 0;
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("DELETE FROM Reservation WHERE reservationId = @ReservationId", connection);

            cmd.Parameters.AddWithValue("@ReservationId", reservationId);

            try
            {
                connection.Open();
                affectedRows = cmd.ExecuteNonQuery();
            }
            catch (SqlException er)
            {
                //oo
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return (affectedRows > 0);
        }

        /// <summary>
        /// returns the reservation with the given id
        /// </summary>
        /// <param name="reservationId"></param>
        /// <returns></returns>
        public static Reservation getById(int reservationId)
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand(BASE_SQL + " WHERE reservationId = @id", connection);
            command.Parameters.AddWithValue("@id", reservationId);

            Reservation reservation = null;
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    reservation = getCurrentReservation(reader);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
                throw ex;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return reservation;
        }

        /// <summary>
        /// creates a Reservationobject based on the data it gets from the datareader
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private static Reservation getCurrentReservation(SqlDataReader reader)
        {
            Reservation reservation = null;
            if (reader != null)
            {
                reservation = new Reservation();
                reservation.ReservationId = (int)reader["ReservationId"];
                reservation.Title = Title.getCurrentTitle(reader);
                reservation.User = User.getCurrentUser(reader);
                reservation.ReserveDate = (DateTime)reader["ReserveDate"];
                reservation.LoanRecalled = (bool)reader["LoanRecalled"];
                reservation.AvailableDate = (reader["AvailableDate"] == DBNull.Value) ? DateTime.MinValue : (DateTime)reader["AvailableDate"];
            }

            return reservation;
        }

        /// <summary>
        /// saves a reservation in the database. depending on the ReservationId it will be inserted or updated. if a new one is created (insert) the ReservationId in the object will be updated
        /// </summary>
        /// <returns></returns>
        public bool save()
        {
            if (this.ReservationId == 0)
            {
                this.ReservationId = Reservation.add(this);
                return (this.ReservationId != 0);
            }
            else
            {
                return Reservation.update(this);
            }
        }

		/// <summary>
		/// Checks if user has a reseravtion on this title. if not a new reservation
		///Create a new reservation object If Avalible set LoanRecalled to true and Avalible Date to current time stamp.
		//Else set LoanRecalled False and And Avalible DateTime.MinValueobject is created and added to yhe database
		/// </summary>
		/// <returns></returns>
        public static bool createReservation(int TitleId, int UserId)
        {
			if (getReservationByTitleAndUser(TitleId, UserId) != null)
			{
				return false;
			}
			else
			{
				Reservation reservation = new Reservation();
				reservation.User = User.getUserByUserId(UserId);
				reservation.Title = Title.getById(TitleId);

				if (reservation.User.IsPendingExpiration) //If the user who wants to reserve is about to expire he is not allowed.
					return false;

				if (reservation.Title != null && reservation.User != null)
				{

					if (Title.isAvalible(TitleId))
					{
						reservation.LoanRecalled = true;
						reservation.AvailableDate = DateTime.Now;

						//notify user that book is available and he has several days to pick it up
						Notification notification = new Notification();
						notification.Type = NotificationType.TITLE_RESERVATION;
						notification.Message = string.Format("You reserved a loanable of {0}. You have time till {1} to pick it up. Otherwise your reservation expires.", reservation.Title.BookTitle, reservation.AvailableDate.AddDays(RESERVATION_EXPIRES_TIMER_IN_DAYS).ToShortDateString());
						notification.Recipient = reservation.User;
						notification.save();

					}
					else
					{
						reservation.LoanRecalled = false;
						reservation.AvailableDate = DateTime.MinValue;

						//notify user reservation has been placed but might take some days...
						Notification notification = new Notification();
						notification.Type = NotificationType.TITLE_RESERVATION;
						notification.Message = string.Format("You reserved a loanable of {0}. We will inform you when a loanable is available for you.", reservation.Title.BookTitle);
						notification.Recipient = reservation.User;
						notification.save();
					}

					reservation.ReserveDate = DateTime.Now;

					return reservation.save();
				}
				else
				{
					return false;
				}
			}
        }

        /// <summary>
        /// returns the reservation which for a given title and user where the available date is set and the loan is recalled
        /// </summary>
        /// <param name="TitleId"></param>
        /// <param name="UserId"></param>
        /// <returns></returns>
        public static Reservation getReservationByTitleAndUser(int TitleId, int UserId)
        {
            string query = BASE_SQL + " WHERE Reservation.TitleId=@TID AND Reservation.UserId=@UID AND Reservation.AvailableDate IS NOT NULL AND Reservation.LoanRecalled = 1";
            Reservation TUreservation = null;
            SqlConnection con = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand(query,con);
            cmd.Parameters.AddWithValue("TID",TitleId);
            cmd.Parameters.AddWithValue("UID", UserId);

            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();

                if (dar.Read())
                {
                    TUreservation = getCurrentReservation(dar);
                }
                dar.Close();

            }
            catch (SqlException err)
            {
                throw err;
            }
            finally
            {
                if (con != null && con.State != ConnectionState.Closed)
                {
                    con.Close();
                }
            }

            return TUreservation;
        }


        public static List<Reservation> getReservationsByUser(int UserId)
        {
            //AND Reservation.AvailableDate IS NOT NULL AND Reservation.LoanRecalled = 1
            string query = BASE_SQL + " WHERE Reservation.UserId=@UID";
            List<Reservation> reservations = new List<Reservation>();
            SqlConnection con = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand(query, con);
            cmd.Parameters.AddWithValue("UID", UserId);

            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();

                while (dar.Read())
                {
                    reservations.Add(getCurrentReservation(dar));
                }
                dar.Close();

            }
            catch (SqlException err)
            {
                throw err;
            }
            finally
            {
                if (con != null && con.State != ConnectionState.Closed)
                {
                    con.Close();
                }
            }

            return reservations;
        }

        public static List<Reservation> getExpiredReservations()
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand(BASE_SQL + " WHERE availableDate < DATEADD(DAY, " + -1 * RESERVATION_EXPIRES_TIMER_IN_DAYS + ", GETDATE())", connection);

            List<Reservation> reservations = new List<Reservation>();
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    reservations.Add(getCurrentReservation(reader));
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
                throw ex;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return reservations;
        }

        /// <summary>
        /// handles all expired reservations. (deletes the reservation and informs the next in the reservation queue that this loanable is now available (sets the availableDate of the reservation to the current time -> the reservation expires time starts for this reservation too)
        /// </summary>
        public static void handleExpiredReservations()
        {
            //?!? throw new NotImplementedException();

            foreach (Reservation expiredReservation in getExpiredReservations())
            {
                if (Title.isReserved(expiredReservation.Title.TitleId, false))
                {
                    Reservation reservationToUpdate = Reservation.getNext(expiredReservation.Title.TitleId, false);

                    reservationToUpdate.LoanRecalled = true;
                    reservationToUpdate.AvailableDate = DateTime.Now;

                    if (Reservation.delete(expiredReservation.ReservationId) && reservationToUpdate.save())
                    {
                        //the old reservation could be deleted and the new one could be updated --> notify new user that his reservation is now active

                        Notification notificationOldReservationExpired = new Notification();
                        notificationOldReservationExpired.Recipient = expiredReservation.User;
                        notificationOldReservationExpired.Message = string.Format("Your reservation for {0} expired and has been deleted", expiredReservation.Title.BookTitle);
                        notificationOldReservationExpired.Type = NotificationType.TITLE_RESERVATION;
                        notificationOldReservationExpired.save();

                        Notification notificationNewReservationPlaced = new Notification();
                        notificationNewReservationPlaced.Recipient = reservationToUpdate.User;
                        notificationNewReservationPlaced.Message = string.Format("You reserved {0}. This book is now available for you. Please pick it up till {1}", reservationToUpdate.Title.BookTitle, reservationToUpdate.AvailableDate.AddDays(RESERVATION_EXPIRES_TIMER_IN_DAYS).ToShortDateString());
                        notificationNewReservationPlaced.Type = NotificationType.TITLE_RESERVATION;
                        notificationNewReservationPlaced.save();
                    }
                }
                else
                {
                    //delete 
                    Reservation.delete(expiredReservation.ReservationId);
                }
            }

            //for each reservation in getExpiredReservations()
            //if title.isReserved(False)
                //reservation.getNext(False) set loanRecalled to True and Avaliblity and notify user
            //reservation.Delete(ExpiredReservation)
        }

        /// <summary>
        /// returns if the user has a reservation for this title where the loan is recalled and availabledate is set
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="titleId"></param>
        /// <returns></returns>
        public static bool userHasAvalibleReservation(int userId, int titleId)
        {
            bool hasReservations = false;
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM reservation WHERE titleId = @TitleId AND userId = @UserId AND loanRecalled = 1 AND availableDate IS NOT NULL", connection);

            cmd.Parameters.AddWithValue("@TitleId", titleId);
            cmd.Parameters.AddWithValue("@UserId", userId);

            try
            {
                connection.Open();
                Object res = cmd.ExecuteScalar();
                if (res != null)
                {
                    hasReservations = ((int)res) > 0;
                }
            }
            catch (SqlException er)
            {
                //oo
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return hasReservations;
        }

        /// <summary>
        /// cancels all reservations at the last day of the semester
        /// </summary>
        public static void cancelReservationsAtEndOfSemester()
        {
            if (DateTime.Now.Date.CompareTo(SemesterDateHelper.getEndOfCurrentSemester().Date) == 0)
            {
                List<Reservation> allReservations = Reservation.getAll();

                foreach (Reservation reservation in allReservations)
                {
                    if (Reservation.delete(reservation.ReservationId))
                    {
                        Notification notification = new Notification();
                        notification.Recipient = reservation.User;
                        notification.SendDate = DateTime.Now;
                        notification.Type = NotificationType.TITLE_RESERVATION;
                        notification.Message = string.Format("Your reservation for {0} has been canceled cause no reservation will be hold over the holidays", reservation.Title.BookTitle);
                        notification.save();
                    }
                }
            }
        }

		/// <summary>
		/// Removes all reservations from specific user
		/// </summary>
        public static bool deleteUserReservations(int userId)
        {
            //get all reservations from user...
            //check for each reservation if there is another reservation for this title (Reservation.getNext(titleId, false))
                //if so, set the loanRecalled field, if the old reservation was available set the available field too and notifiy the user
            //delete the old reservation and notify user

            List<Reservation> usersReservations = Reservation.getReservationsByUser(userId);
            bool workedForAll = true;
            foreach (Reservation reservation in usersReservations)
            {
                Reservation nextReservationForThisTitle = Reservation.getNext(reservation.Title.TitleId, false);
                if (nextReservationForThisTitle != null)
                {
                    nextReservationForThisTitle.LoanRecalled = true;

                    if (reservation.AvailableDate != DateTime.MinValue)
                    {
                        nextReservationForThisTitle.AvailableDate = DateTime.Now;
                        //send notification...

                        Notification notification = new Notification();
                        notification.Recipient = nextReservationForThisTitle.User;
                        notification.SendDate = DateTime.Now;
                        notification.Type = NotificationType.TITLE_RESERVATION;
                        notification.Message = string.Format("You reserved {}. A loanable is now available now for you. Please pick it up till {}", reservation.Title.BookTitle, reservation.AvailableDate.ToShortDateString());
                        notification.save();
                    }
                    nextReservationForThisTitle.save();
                }

                if (!Reservation.delete(reservation.ReservationId))
                {
                    workedForAll = false;
                }
            }

            return workedForAll;
        }

        /// <summary>
        /// returns the last reservation in the reservation queue for a title where the loanable is recalled
        /// </summary>
        /// <param name="titleId"></param>
        /// <returns></returns>
        public static Reservation getLatestRecalledInReservationQueue(int titleId)
        {
            string query = BASE_SQL + " WHERE title.titleId = @titleId AND loanRecalled = 1 ORDER BY ReserveDate DESC";
            SqlConnection con = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand(query, con);

            Reservation reservation = null;
            List<Reservation> allReservations = new List<Reservation>();

            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();

                while (dar.Read())
                {
                    allReservations.Add(getCurrentReservation(dar));
                }

                if (allReservations.Count > 0)
                {
                    reservation = allReservations[allReservations.Count - 1];
                }

                dar.Close();
            }
            catch (SqlException err)
            {
                throw err;
            }
            finally
            {
                if (con != null && con.State != ConnectionState.Closed)
                {
                    con.Close();
                }
            }

            return reservation;
        }

        /// <summary>
        /// returns the last reservation where loan is recalled and the availableDate set
        /// </summary>
        /// <param name="titleId"></param>
        /// <returns></returns>
        public static Reservation getNewestReservationWithAvailableLoanable(int titleId) 
        {
            string query = BASE_SQL + " WHERE title.titleId = @titleId AND loanRecalled = 1 AND AvailableDate IS NOT NULL ORDER BY AvailableDate ASC";
            SqlConnection con = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand(query, con);
            cmd.Parameters.AddWithValue("@titleId", titleId);


            Reservation reservation = null;

            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();

                if (dar.Read())
                {
                    reservation = getCurrentReservation(dar);
                }

                dar.Close();
            }
            catch (SqlException err)
            {
                throw err;
            }
            finally
            {
                if (con != null && con.State != ConnectionState.Closed)
                {
                    con.Close();
                }
            }

            return reservation;
        }

		public static List<Reservation> getReservationsByTitleId(int titleId)
		{
			List<Reservation> result = new List<Reservation>();
			SqlConnection connection = new SqlConnection(Settings.ConnectionString);
			SqlCommand cmd = new SqlCommand("SELECT * FROM Reservation WHERE TitleId = @TitleId", connection);
			cmd.Parameters.AddWithValue("@TitleId", titleId);

			try
			{
				connection.Open();
				SqlDataReader reader = cmd.ExecuteReader();

				while (reader.Read())
				{
					result.Add(getCurrentReservation(reader));
				}
				reader.Close();
			}
			catch (SqlException er)
			{
				throw er;
			}
			finally
			{
				if (connection != null && connection.State != ConnectionState.Closed)
				{
					connection.Close();
				}
			}

			return result;
		}
    }
}
